1 Imports System.Data.SqlClient
2 Public Class frmDocument
3
4 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
5 Me.Close()
6 End Sub
7 Sub Reset()
8 txtDocument.Text = ""
9 txtID.Text = ""
10 btnSave.Enabled = True
11 btnDelete.Enabled = False
12 btnUpdate.Enabled = False
13 txtDocument.Focus()
14 auto()
15 End Sub
16 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
17 Reset()
18 End Sub
19 Private Sub auto()
20 Try
21 Dim Num As Integer = 0
22 con = New SqlConnection(cs)
23 con.Open()
24 Dim sql As String = ("SELECT MAX(DOC_ID) FROM Document")
25 cmd = New SqlCommand(sql)
26 cmd.Connection = con
27 If (IsDBNull(cmd.ExecuteScalar)) Then
28 Num = 1
29 txtID.Text = Num.ToString
30 Else
31 Num = cmd.ExecuteScalar + 1
32 txtID.Text = Num.ToString
33 End If
34 cmd.Dispose()
35 con.Close()
36 con.Dispose()
37 Catch ex As Exception
38 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
39 End Try
40 End Sub
41
42 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
43 If txtDocument.Text = "" Then
44 MessageBox.Show("Please enter document", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
45 txtDocument.Focus()
46 Return
47 End If
48
49 Try
50 con = New SqlConnection(cs)
51 con.Open()
52 Dim ct As String = "select Docname from document where Docname=@d1"
53 cmd = New SqlCommand(ct)
54 cmd.Parameters.AddWithValue("@d1", txtDocument.Text)
55 cmd.Connection = con
56 rdr = cmd.ExecuteReader()
57
58 If rdr.Read() Then
59 MessageBox.Show("Document Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
60 txtDocument.Text = ""
61 txtDocument.Focus()
62 If (rdr IsNot Nothing) Then
63 rdr.Close()
64 End If
65 Return
66 End If
67
68 con = New SqlConnection(cs)
69 con.Open()
70
71 Dim cb As String = "insert into document(Doc_ID,Docname) VALUES (" & txtID.Text & ",@d1)"
72 cmd = New SqlCommand(cb)
73 cmd.Parameters.AddWithValue("@d1", txtDocument.Text)
74 cmd.Connection = con
75 cmd.ExecuteReader()
76 con.Close()
77 Dim st As String = "added the new document '" & txtDocument.Text & "'"
78 LogFunc(lblUser.Text, st)
79 MessageBox.Show("Successfully Saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
80 btnSave.Enabled = False
81 Getdata()
82 Catch ex As Exception
83 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
84 End Try
85 End Sub
86
87 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
88 If txtDocument.Text = "" Then
89 MessageBox.Show("Please enter document", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
90 txtDocument.Focus()
91 Return
92 End If
93
94 Try
95
96 con = New SqlConnection(cs)
97 con.Open()
98
99 Dim cb As String = "Update document set Docname=@d1 where Doc_ID=@d2"
100 cmd = New SqlCommand(cb)
101 cmd.Connection = con
102 cmd.Parameters.AddWithValue("@d1", txtDocument.Text)
103 cmd.Parameters.AddWithValue("@d2", txtID.Text)
104 cmd.ExecuteReader()
105 con.Close()
106 Dim st As String = "updated the document '" & txtDocument.Text & "'"
107 LogFunc(lblUser.Text, st)
108 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
109 btnUpdate.Enabled = False
110 Getdata()
111 Catch ex As Exception
112 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
113 End Try
114 End Sub
115 Private Sub DeleteRecord()
116
117 Try
118 Dim RowsAffected As Integer = 0
119 con = New SqlConnection(cs)
120 con.Open()
121 Dim cl As String = "select Doc_ID from StudentDocSubmitted,document where StudentDocSubmitted.docId=document.Doc_ID and Doc_ID=@d1"
122 cmd = New SqlCommand(cl)
123 cmd.Connection = con
124 cmd.Parameters.AddWithValue("@d1", txtID.Text)
125 rdr = cmd.ExecuteReader()
126 If rdr.Read Then
127 MessageBox.Show("Unable to delete..Already in use in Student Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
128 If Not rdr Is Nothing Then
129 rdr.Close()
130 End If
131 Exit Sub
132 End If
133 con = New SqlConnection(cs)
134 con.Open()
135 Dim cq As String = "delete from document where Doc_id=@d1"
136 cmd = New SqlCommand(cq)
137 cmd.Parameters.AddWithValue("@d1", txtID.Text)
138 cmd.Connection = con
139 RowsAffected = cmd.ExecuteNonQuery()
140 If RowsAffected > 0 Then
141 Dim st As String = "deleted the document '" & txtDocument.Text & "'"
142 LogFunc(lblUser.Text, st)
143 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
144 Getdata()
145 Reset()
146 Else
147 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
148 Reset()
149 End If
150 If con.State = ConnectionState.Open Then
151 con.Close()
152
153 End If
154 Catch ex As Exception
155 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
156 End Try
157 End Sub
158 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
159 Try
160 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
161 DeleteRecord()
162 End If
163 Catch ex As Exception
164 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
165 End Try
166 End Sub
167
168 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
169 Try
170 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
171 txtID.Text = dr.Cells(0).Value.ToString()
172 txtDocument.Text = dr.Cells(1).Value.ToString()
173 btnUpdate.Enabled = True
174 btnDelete.Enabled = True
175 btnSave.Enabled = False
176 Catch ex As Exception
177 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
178 End Try
179 End Sub
180
181 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
182 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
183 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
184 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
185 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
186 End If
187 Dim b As Brush = SystemBrushes.ControlText
188 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
189
190 End Sub
191 Public Sub Getdata()
192 Try
193 con = New SqlConnection(cs)
194 con.Open()
195 cmd = New SqlCommand("SELECT RTRIM(DOC_ID),RTRIM(Docname) from document order by Docname", con)
196 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
197 dgw.Rows.Clear()
198 While (rdr.Read() = True)
199 dgw.Rows.Add(rdr(0), rdr(1))
200 End While
201 con.Close()
202 Catch ex As Exception
203 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
204 End Try
205 End Sub
206
207 Private Sub frmDocname_Load_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
208 Getdata()
209 End Sub
210 End Class
